﻿﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
﻿using System.Windows;
﻿using System.Windows.Markup;
﻿using MagicKey.Components.FileAccess;
﻿using MagicKey.Components.UserManager;
﻿using MagicKey.Interfaces;
﻿using MySql.Data.MySqlClient;

namespace MagicKey.Components.Database
{
    class MagicDatabase : IDatabase
    {

        private MySqlCommand _cmd;
        private MySqlConnection _conn = null;
        private string _commandLine;
        private string _databaseName;

        /// <summary>
        /// 
        /// </summary>
        public MagicDatabase()
        {
            this.DatabaseName = "dba";
            Connect();
        }

        public MagicDatabase(string databaseName)
        {

            this.DatabaseName = databaseName;
            Connect();
        }

        public string DatabaseName
        {
            get
            {
                return this._databaseName;
            }

            set
            {
                this._databaseName = value;
            }
        }

        /// <summary>
        /// Connects to the database.
        /// </summary>
        public void Connect()
        {
            _conn = new MySqlConnection(@"server=localhost;userid=root;password='';database=" + this._databaseName);

            try
            {
                _cmd = new MySqlCommand();
                //Create a connection  
                _cmd.Connection = _conn;
                //Open the connection  
                _cmd.Connection.Open();
            }

            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }

            finally
            {
                if (_conn != null)
                {
                    _cmd.Connection.Close();
                }
            }
        }

        /// <summary>
        /// Inserts an user with his parameters in the database.
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <param name="salt"></param>
        /// <param name="puKey"></param>
        /// <param name="prKey"></param>
        /// <param name="idUserRole"></param>
        /// <returns>True falls ein User erfolgreich hinzugefügt wurde, bzw. falls es fehlgeschlagen war.</returns>
        public bool InsertUser(string username, string password, string salt, string puKey, string prKey, int idUserRole)
        {
            bool result = false;
            _commandLine =
                @"INSERT INTO User_sit(username, password, salt, pu_key, pr_key, id_User_role) VALUES (?username, ?password, ?salt, ?pu_key, ?pr_key, ?id_User_role);";

            _cmd.CommandText = _commandLine;

            _cmd.Parameters.Add("?username", MySqlDbType.VarChar).Value = username;
            _cmd.Parameters.Add("?password", MySqlDbType.VarChar).Value = password;
            _cmd.Parameters.Add("?salt", MySqlDbType.VarChar).Value = salt;
            _cmd.Parameters.Add("?pu_key", MySqlDbType.VarChar).Value = puKey;
            _cmd.Parameters.Add("?pr_key", MySqlDbType.VarChar).Value = prKey;
            _cmd.Parameters.Add("?id_User_role", MySqlDbType.Int32).Value = idUserRole;


            try
            {
                //Open a connection  
                _cmd.Connection.Open();
                //Execute the command 
                _cmd.ExecuteNonQuery();
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
                result = true;
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
            }
            return result;
        }

        /// <summary>
        /// Insert authorization to access the file (give authorization).
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="recipient"></param>
        /// <param name="file"></param>
        /// <param name="encryptedSymetricKeyOfRecipient"></param>
        /// <param name="encryptedSymetricKeyOfSender"></param>
        /// <returns>True if the insertion was successfull or false if it failed.</returns>
        public bool InsertAuthorization(int sender, int recipient, int file, string encryptedSymetricKeyOfRecipient, string encryptedSymetricKeyOfSender)
        {
            bool result = false;
            _commandLine =
                @"INSERT INTO Authorization(id_Owner, id_User, id_file, encryptedSymetricKeyOfRecipient, encryptedSymetricKeyOfSender) VALUES (?id_Owner, ?id_User, ?id_file, ?encryptedSymetricKeyOfRecipient, ?encryptedSymetricKeyOfSender);";

            _cmd.CommandText = _commandLine;

            _cmd.Parameters.Add("?id_Owner", MySqlDbType.Int32).Value = sender;
            _cmd.Parameters.Add("?id_User", MySqlDbType.Int32).Value = recipient;
            _cmd.Parameters.Add("?id_file", MySqlDbType.Int32).Value = file;
            _cmd.Parameters.Add("?encryptedSymetricKeyOfRecipient", MySqlDbType.String).Value = encryptedSymetricKeyOfRecipient;
            _cmd.Parameters.Add("?encryptedSymetricKeyOfSender", MySqlDbType.String).Value = encryptedSymetricKeyOfSender;

            try
            {
                //Open a connection  
                _cmd.Connection.Open();
                //Execute the command 
                _cmd.ExecuteNonQuery();
                result = true;
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
            }
            return result;
        }

        /// <summary>
        /// Removes authorization to access the files.
        /// </summary>
        /// <param name="userId"></param>
        /// <returns>True falls es gelöscht wurde bzw. False falls es fehlgeschlagen war</returns>
        public bool RemoveAuthorization(int userId)
        {
            bool result = false;
            try
            {
                _commandLine = @"DELETE FROM Authorization WHERE id_User = ?user;";

                _cmd.CommandText = _commandLine;
                _cmd.Parameters.Add("?user", MySqlDbType.VarChar).Value = userId;

                //Open a connection  
                _cmd.Connection.Open();
                //Execute the command 
                _cmd.ExecuteNonQuery();

                result = true;
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
            }
            return result;
        }

        /// <summary>
        /// Hiermit wird ein Datei in der Datenbank gelöscht.
        /// </summary>
        /// <returns>true falls das Datei erfolgreich gelöscht wurde, bzw. falls es fehlgeschlagen war</returns>
        public bool DeleteFile(string path, int userId)
        {
            bool result = false;
            MySqlDataReader reader;
            try
            {
                _commandLine =
                @"delete from file where id IN(select * from (select a.id_file from authorization a where a.id_Owner = a.id_User AND id_User = ?userId AND a.id_file IN(Select f.id from file f where f.path = ?path))AS tmp)";

                _cmd.CommandText = _commandLine;
                _cmd.Parameters.Add("?userId", MySqlDbType.Int32).Value = userId;
                _cmd.Parameters.Add("?path", MySqlDbType.VarChar).Value = path;

                //Open a connection  

                _cmd.Connection.Open();

                //Execute the command 
                _cmd.ExecuteNonQuery();
                result = true;
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="type"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public string LoadParameterUser(string type, string username)
        {
            string key = null;
            switch (type)
            {
                case "symetricKey":
                    return GetSymetricKey(username);
                case "privateKey":
                    _commandLine =
                @"SELECT pr_key FROM user_sit WHERE username = ?username;";
                    break;
                case "publicKey":
                    _commandLine =
                @"SELECT pu_key FROM user_sit WHERE username = ?username;";
                    break;
                case "password":
                    _commandLine =
                @"SELECT password FROM user_sit WHERE username = ?username;";
                    break;
                case "salt":
                    _commandLine =
                @"SELECT salt FROM user_sit WHERE username = ?username;";
                    break;
                case "allUsers":
                    _commandLine =
                @"SELECT username FROM user_sit WHERE 1 = ?username;";
                    break;

                case "role":
                    _commandLine =
                @"SELECT r.role FROM user_role r JOIN user_sit u ON r.id = u.id_User_role WHERE u.username = ?username;";
                    break;
            }

            try
            {
                _cmd.CommandText = _commandLine;
                _cmd.Parameters.Add("?username", MySqlDbType.VarChar).Value = username;

                //Open a connection  
                _cmd.Connection.Open();
                //Execute the command 
                MySqlDataReader reader = _cmd.ExecuteReader();
                int index = 0;
                while (reader != null && reader.Read())
                {
                    if (index > 0)
                    {
                        key += ",";
                    }
                    index++;
                    key += reader.GetString(0);
                }
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
            }

            return key;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        private string GetSymetricKey(string username)
        {
            var userRole = LoadParameterUser("role", username);

            switch (userRole)
            {
                case "Student":
                    return "pqsqczwwwQTBMBiShN9zsA==";
                case "Professor":
                    return "cOeKG4YG1eSyNpll+3ax6Q==";
                case "Employee":
                    return "CacnuZgdt2JMx2voDkFJ8Q==";
                case "Guest":
                    return "JSs6P4r7HpOBiIDBVeZv9A==";
            }
            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<User> LoadAllUsers()
        {
            List<User> userList = null;
            List<string> userFiles;

            try
            {
                _commandLine =
                @"SELECT u.id , username, pu_key, r.role FROM user_sit u JOIN user_role r ON r.id = u.id_User_role;";
                _cmd.CommandText = _commandLine;

                //Open a connection  
                _cmd.Connection.Open();
                //Execute the command 
                MySqlDataReader reader = _cmd.ExecuteReader();

                userList = new List<User>();
                while (reader != null && reader.Read())
                {
                    var user = new User()
                    {
                        Id = reader.GetInt32(0),
                        Name = reader.GetString(1),
                        PublicKey = reader.GetString(2),
                        Role = reader.GetString(3)
                    };
                    userList.Add(user);
                }
                _cmd.Parameters.Clear();
                //Close the connection  
                _cmd.Connection.Close();


                // Load user own files
                foreach (User u in userList)
                {
                    _commandLine =
                @"SELECT f.path FROM FILE f JOIN authorization a ON f.id = a.id_file where id_Owner = id_User and id_Owner = ?id;";
                    _cmd.CommandText = _commandLine;
                    _cmd.Parameters.Add("?id", MySqlDbType.Int32).Value = u.Id;

                    //Open a connection  
                    _cmd.Connection.Open();
                    //Execute the command 
                    reader = _cmd.ExecuteReader();

                    u.MyFiles = new List<string>();
                    while (reader != null && reader.Read())
                    {
                        u.MyFiles.Add(reader.GetString(0));
                    }

                    _cmd.Parameters.Clear();
                    //Close the connection  
                    _cmd.Connection.Close();
                }

                // Load other user files
                foreach (User u in userList)
                {
                    _commandLine =
                @"SELECT f.path FROM FILE f JOIN authorization a ON f.id = a.id_file where id_User = ?id and id_User != id_Owner;";
                    _cmd.CommandText = _commandLine;
                    _cmd.Parameters.Add("?id", MySqlDbType.Int32).Value = u.Id;

                    //Open a connection  
                    _cmd.Connection.Open();
                    //Execute the command 
                    reader = _cmd.ExecuteReader();

                    u.OtherUserFiles = new List<string>();
                    while (reader != null && reader.Read())
                    {
                        u.OtherUserFiles.Add(reader.GetString(0));
                    }
                    //Close the connection  
                    _cmd.Parameters.Clear();
                    _cmd.Connection.Close();
                }
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
            }

            return userList;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public string GetUserId(string username, string password)
        {
            string userId = null;
            _commandLine = @"SELECT id FROM user WHERE username = ?username AND password = ?password;";

            _cmd.CommandText = _commandLine;
            _cmd.Parameters.Add("?username", MySqlDbType.VarChar).Value = username;
            _cmd.Parameters.Add("?password", MySqlDbType.VarChar).Value = password;

            try
            {
                //Open a connection  
                _cmd.Connection.Open();
                //Execute the command 
                MySqlDataReader reader = _cmd.ExecuteReader();

                while (reader != null && reader.Read())
                {
                    userId = reader.GetString(0);
                }
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }

            return userId;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public int GetFileId(string path)
        {
            int fileId = 0;
            _commandLine = @"SELECT id FROM file WHERE path = ?path;";

            _cmd.CommandText = _commandLine;
            _cmd.Parameters.Add("?path", MySqlDbType.VarChar).Value = path;

            try
            {
                //Open a connection  
                _cmd.Connection.Open();
                //Execute the command 
                MySqlDataReader reader = _cmd.ExecuteReader();

                while (reader != null && reader.Read())
                {
                    fileId = reader.GetInt32(0);
                }
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
            }

            return fileId;
        }

        /// <summary>
        /// Hiermit wird ein Datei in der Datenbank hinzugefügt
        /// </summary>
        /// <param name="path"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int InsertFile(string path, int userId)
        {
            int fileId = 0;
            string filename = path.Substring(path.LastIndexOf('/') + 1);

            try
            {
                //------------------- Insert the new file in the database.

                _commandLine = @"INSERT INTO file(filename, path) VALUES (?filename, ?path);";

                _cmd.CommandText = _commandLine;
                _cmd.Parameters.Add("?filename", MySqlDbType.VarChar).Value = filename;
                _cmd.Parameters.Add("?path", MySqlDbType.VarChar).Value = path;

                //Open a connection  
                _cmd.Connection.Open();
                //Execute the command 
                _cmd.ExecuteNonQuery();
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();


                //------------------- Load the identifier of the new inserted file.

                _commandLine = @"SELECT id FROM file WHERE file.filename = ?filename";

                _cmd.CommandText = _commandLine;
                _cmd.Parameters.Add("?filename", MySqlDbType.VarChar).Value = filename;

                //Open a connection  
                _cmd.Connection.Open();
                //Execute the command 
                MySqlDataReader reader = _cmd.ExecuteReader();

                while (reader != null && reader.Read())
                {
                    fileId = reader.GetInt32(0);
                }
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();


                //------------------- Insert the identifier of the file in the user_file table 
                //------------------- the user is the owner.


                //this.InsertAuthorization(userId, userId, fileId);
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
            }
            return fileId;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="path"></param>
        /// <returns></returns>
        public bool HasAuthorization(int userId, string path)
        {
            int fileId = 0;
            bool result = false;
            _commandLine = @"SELECT a.id_User, a.id_file FROM authorization a WHERE a.id_User = ?userId 
AND a.id_file IN (SELECT id FROM file WHERE path = ?path);";

            _cmd.CommandText = _commandLine;
            _cmd.Parameters.Add("?userId", MySqlDbType.Int32).Value = userId;
            _cmd.Parameters.Add("?path", MySqlDbType.VarChar).Value = path;

            try
            {
                //Open a connection  
                _cmd.Connection.Open();
                //Execute the command 
                MySqlDataReader reader = _cmd.ExecuteReader();

                while (reader != null && reader.Read())
                {
                    userId = reader.GetInt32(0);
                    fileId = reader.GetInt32(0);
                }

                if (fileId != 0)
                    result = true;

            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
            }

            return result;
        }

        public string GetOwnerName(string path)
        {
            string ownerName = null;

            _commandLine = @"SELECT u.username FROM user_sit u JOIN Authorization a ON u.id = a.id_Owner WHERE
a.id_file IN (SELECT id FROM file WHERE path = ?path);";

            _cmd.CommandText = _commandLine;
            _cmd.Parameters.Add("?path", MySqlDbType.VarChar).Value = path;

            try
            {
                //Open a connection  
                _cmd.Connection.Open();
                //Execute the command 
                MySqlDataReader reader = _cmd.ExecuteReader();

                while (reader != null && reader.Read())
                {
                    ownerName = reader.GetString(0);
                }

            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
            }

            return ownerName;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="path"></param>
        /// <param name="userId"></param>
        /// <param name="isOwner"></param>
        /// <returns></returns>
        public string LoadSymetricKeyOfFile(string path, int userId, bool isOwner)
        {
            string result = null;

            if (isOwner)
            {
                _commandLine =
                    @"Select Distinct encryptedSymetricKeyOfSender FROM File f join Authorization a ON f.id = a.id_File WHERE f.path = ?path AND a.id_User = ?userId;";

            }

            else
            {
                _commandLine =
                     @"Select Distinct encryptedSymetricKeyOfRecipient FROM File f join Authorization a ON f.id = a.id_File WHERE f.path = ?path AND a.id_User = ?userId;";
            }


            _cmd.CommandText = _commandLine;

            _cmd.Parameters.Add("?path", MySqlDbType.VarChar).Value = path;

            _cmd.Parameters.Add("?userId", MySqlDbType.VarChar).Value = userId;

            try
            {
                //Open a connection  
                _cmd.Connection.Open();

                //Execute the command 
                MySqlDataReader reader = _cmd.ExecuteReader();

                while (reader != null && reader.Read())
                {
                    result = reader.GetString(0);
                }

            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }

            finally
            {
                //clear parameters  
                _cmd.Parameters.Clear();

                //close connection
                _cmd.Connection.Close();
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string LoadAllEntries()
        {
            const string delimiterLinie = "\r\n\r\n############################################################\r\n\r\n";
            string allEntries = string.Empty;

            var allQueries = new List<string>
            {
                @"SELECT * FROM user_sit;",
                @"SELECT * FROM user_role;",
                @"SELECT * FROM user_file;",
                @"SELECT * FROM file;",
                @"SELECT * FROM authorization;"
            };

            // Initialize all queries

            var allColumns = new List<string>
            {
                "#id,username,password,salt,pu_key,pr_key,id_User_role\r\n;",
                "#id,role,symmetric_key;",
                "#id_user,id_file;",
                "#id,filename,path\r\n;",
                "id_owner,id_user,id_file\r\n;"
            };
            //Initialize all columns sort with table

            try
            {
                foreach (string query in allQueries)
                {
                    int index;
                    MySqlDataReader reader;
                    if (query.Contains("user_sit"))
                    {
                        allEntries += allColumns[0];
                        _commandLine = query;
                        _cmd.CommandText = _commandLine;

                        //Open a connection  
                        _cmd.Connection.Open();
                        //Execute the command 
                        reader = _cmd.ExecuteReader();

                        index = 0;
                        while (reader != null && reader.Read())
                        {
                            allEntries += reader.GetInt32(index++) + ",";
                            allEntries += reader.GetString(index++) + ",";
                            allEntries += reader.GetString(index++) + ",";
                            allEntries += reader.GetString(index++) + ",";
                            allEntries += reader.GetString(index++) + ",";
                            allEntries += reader.GetString(index++) + ",";
                            allEntries += reader.GetInt32(index++);
                        }
                        allEntries += delimiterLinie;

                        _cmd.Parameters.Clear();
                        //Close the connection  
                        _cmd.Connection.Close();
                    }

                    if (query.Contains("user_role"))
                    {
                        allEntries += allColumns[1];
                        _commandLine = query;
                        _cmd.CommandText = _commandLine;

                        //Open a connection  
                        _cmd.Connection.Open();
                        //Execute the command 
                        reader = _cmd.ExecuteReader();

                        index = 0;
                        while (reader != null && reader.Read())
                        {
                            allEntries += reader.GetInt32(index++) + ",";
                            allEntries += reader.GetString(index++) + ",";
                            allEntries += reader.GetString(index++);
                        }
                        allEntries += delimiterLinie;

                        _cmd.Parameters.Clear();
                        //Close the connection  
                        _cmd.Connection.Close();
                    }

                    if (query.Contains("user_file"))
                    {
                        allEntries += allColumns[2];
                        _commandLine = query;
                        _cmd.CommandText = _commandLine;

                        //Open a connection  
                        _cmd.Connection.Open();
                        //Execute the command 
                        reader = _cmd.ExecuteReader();

                        index = 0;
                        while (reader != null && reader.Read())
                        {
                            allEntries += reader.GetInt32(index++) + ",";
                            allEntries += reader.GetInt32(index++);
                        }
                        allEntries += delimiterLinie;

                        _cmd.Parameters.Clear();
                        //Close the connection  
                        _cmd.Connection.Close();
                    }

                    if (query.Contains("file"))
                    {
                        allEntries += allColumns[3];
                        _commandLine = query;
                        _cmd.CommandText = _commandLine;

                        //Open a connection  
                        _cmd.Connection.Open();
                        //Execute the command 
                        reader = _cmd.ExecuteReader();

                        index = 0;
                        while (reader != null && reader.Read())
                        {
                            allEntries += reader.GetInt32(index++) + ",";
                            allEntries += reader.GetString(index++) + ",";
                            allEntries += reader.GetString(index++);
                        }
                        allEntries += delimiterLinie;

                        _cmd.Parameters.Clear();
                        //Close the connection  
                        _cmd.Connection.Close();
                    }

                    if (query.Contains("authorization"))
                    {
                        allEntries += allColumns[4];
                        _commandLine = query;
                        _cmd.CommandText = _commandLine;

                        //Open a connection  
                        _cmd.Connection.Open();
                        //Execute the command 
                        reader = _cmd.ExecuteReader();

                        index = 0;
                        while (reader != null && reader.Read())
                        {
                            allEntries += reader.GetInt32(index++) + ",";
                            allEntries += reader.GetInt32(index++) + ",";
                            allEntries += reader.GetInt32(index++);
                        }
                        allEntries += delimiterLinie;

                        _cmd.Parameters.Clear();
                        //Close the connection  
                        _cmd.Connection.Close();
                    }

                }
            }
            catch (MySqlException ex)
            {
                //MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                //Close the connection  
                _cmd.Parameters.Clear();
                _cmd.Connection.Close();
            }

            return allEntries;
        }

    }
}
